﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration; 
namespace DAL
{
    public class ReDAL
    {
#pragma warning disable CS0618 // “ConfigurationSettings.AppSettings”已过时:“This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings”
        public static string conn = ConfigurationSettings.AppSettings["con"].ToString();
#pragma warning restore CS0618 // “ConfigurationSettings.AppSettings”已过时:“This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings”
        //通过openid获取Ryxx
        public static DataTable GetRyxx(string Opid)
        {
            DataTable data = new DataTable();
            SqlParameter[] sp = new SqlParameter[]
            {
                 new SqlParameter("@Opid",Opid)
            };
            try
            {
                string sql = @"SELECT RYXX.IMAGES,RYXX.RYID,RYXX.RYBH,RYXX.RFKH,RYXX.RYID FROM RYXX,IDBIND WHERE RYXX.RYID=IDBIND.RYID AND OPENID=@Opid";
                 return SqlHelper.ExecuteDataSet(conn,CommandType.Text,sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("报错GetRyxx："+ex.Message);
                return data;
            }


        }
        //修改图片
        public static int UpdateImages(string ryid,string images)
        {
            DataTable data = new DataTable();
            SqlParameter[] sp = new SqlParameter[]
            {
                 new SqlParameter("@ryid",ryid),
                 new SqlParameter("@images",images)
            };
            try
            {
                string sql = "UPDATE RYXX SET zp= @images, IMAGES =@images WHERE RYID=@ryid";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sp);
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("UpdateImages：" + ex.Message);
                return -1;
            }


        }
        //查询ryid
        public static string SelectRyid(string Opid)
        {
            SqlDataReader rs = null;
            SqlParameter[] sp = new SqlParameter[]
            {
            new SqlParameter("@Opid",Opid)
            };
            string sql = "select ryid from dbo.IdBind where openid=@Opid";
            rs = SqlHelper.ExecuteReader(conn,CommandType.Text, sql, sp);
            string rfkh = "";
            while (rs.Read())
            {
                rfkh = rs["ryid"].ToString();
            }
         
            return rfkh;
        }
        //通过ryid获取订单号
        public static string OrderRyid(string Order)
        {
            SqlDataReader rs = null;
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@Order",Order)
        };
            string sql = @"select top(1) [Order], MAX(DateTimes) from dbo.OrderNumber    where Ryid in(select ryid from ryxx where ryid=@Order)  GROUP BY [Order],DateTimes  order by DateTimes desc";
            rs = SqlHelper.ExecuteReader(conn,CommandType.Text,sql,  sp);
            string Or = "";
            while (rs.Read())
            {
                Or = rs["Order"] == DBNull.Value ? "" : rs["Order"].ToString();
            }

            return Or;
        }
        public static int IAUpdate(string openid, string money)
        {
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@openid",openid)
        };
            string sql = @"Update dbo.ryxx set bmmc='部门111 " + money + " ' where rfkh in(select rfkh from dbo.IdBind where openid=@openid)";
            return SqlHelper.ExecuteNonQuery(conn,CommandType.Text,sql,  sp);
        }
        //查询卡号
        public static string SelectRfkh(string Opid)
        {
            SqlDataReader rs = null;
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@Opid",Opid)
        };
            string sql = @"select rfkh from dbo.IdBind where openid=@Opid";
            rs = SqlHelper.ExecuteReader(conn,CommandType.Text,sql,  sp);
            string rfkh = "";
            while (rs.Read())
            {
                rfkh = rs["rfkh"].ToString();
            }

            return rfkh;
        }
        //添加订单号
        public static int OrderNum1(string order, string DateTimesSuccess, int State)
        {
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@Order",order),
        new SqlParameter("@DateTimesSuccess",DateTimesSuccess),
        new SqlParameter("@State",State)
        };
            string sql = @"UPDATE dbo.OrderNumber SET DateTimesSuccess=@DateTimesSuccess,[State]=@State WHERE [Order]=@Order";
            return SqlHelper.ExecuteNonQuery(conn,CommandType.Text, sql,  sp);
        }
        public static int OrderNum(string order, double money, string ryid, DateTime dateTimes, int State)
        {
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@Order",order),
        new SqlParameter("@Moneys",money),
        new SqlParameter("@Ryid",ryid),
        new SqlParameter("@DateTimes",dateTimes),
        new SqlParameter("@State",State)
        };
            string sql = "insert into dbo.OrderNumber ([Order], Moneys, Ryid,DateTimes,State)values(@Order,@Moneys,@Ryid,@DateTimes,@State)";
            return SqlHelper.ExecuteNonQuery(conn, CommandType.Text,sql,  sp);
        }
        //充值增加
        public static int ExecuteLiuShui(string rfkh, double bdje)
        {
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@rfkh",rfkh),
        new SqlParameter("@bdje",bdje)
        };
            string sql = "update ryxx set rfye=rfye+@bdje,grye=grye+@bdje where rfkh=@rfkh";
            int num = 0;
            num = SqlHelper.ExecuteNonQuery(conn, CommandType.Text,sql,  sp);
            return num;
        }
        //将充值添加lssj记录
        public static int Flowrecord(string OpId, double moneys, string czlx)
        {
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@openid",OpId),
        new SqlParameter("@czje",moneys),
        new SqlParameter("@czlx",czlx)
        };
            string sql = "usp_498Recharge";
            return SqlHelper.ExecuteNonQuery(conn,System.Data.CommandType.StoredProcedure, sql, sp);
        }
        //获取订单号
        public static string OrderNu(string Order)
        {
            SqlDataReader rs = null;
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@Order",Order)
        };
            string sql = "select top(1) [Order], MAX(DateTimes) from dbo.OrderNumber    where Ryid in(select ryid from ryxx where rfkh=@Order)  GROUP BY [Order],DateTimes  order by DateTimes desc";
            rs = SqlHelper.ExecuteReader(conn, CommandType.Text,sql,  sp);
            string Or = "";
            while (rs.Read())
            {
                Or = rs["Order"] == DBNull.Value ? "" : rs["Order"].ToString();
            }
        
            return Or;
        }
        //通过Opid获取ryid
        public static string ObtainRfkh(string Opid)
        {
            SqlDataReader rs = null;
            SqlParameter[] sp = new SqlParameter[]
            {
        new SqlParameter("@Opid",Opid)
            };
            string sql = "select ryid from ryxx where ryid in(select ryid from dbo.IdBind where openid=@Opid)";
            rs = SqlHelper.ExecuteReader(conn, CommandType.Text,sql,  sp);
            string ryid = "";
            if (rs.Read())
            {
                ryid = rs["ryid"].ToString();
            }

            return ryid;
        }
        //通过商户订单号获取Opid
        public static string ObtainOpid(string Order)
        {
            try
            {
                SqlDataReader rs = null;
                SqlParameter[] sp = new SqlParameter[]
                {
           new SqlParameter("@Order",Order)
                };
                string sql = @"select openid from dbo.IdBind inner join ryxx on dbo.IdBind.ryid=ryxx.ryid inner join dbo.OrderNumber on dbo.IdBind.ryid= OrderNumber.Ryid where [Order]=@Order";
                rs = SqlHelper.ExecuteReader(conn, CommandType.Text,sql,  sp);
                string openid = "";
                if (rs.Read())
                {
                    openid = rs["openid"] == DBNull.Value ? "" : rs["openid"].ToString();
                }
         
                return openid;
            }
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            catch (Exception ex)
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
            {

                throw;
            }

        }
        //获取OrderNumber表ID列
        public static int NumID()
        {
            string sql = "select count(*)+1 from dbo.OrderNumber";
            return (Int32) SqlHelper.ExecuteScalar(conn, CommandType.Text,sql);
        }
    }
}
